package cn.smvc.sys.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.stereotype.Repository;

import cn.smvc.common.dao.JDBCUtils;
import cn.smvc.common.utils.StringUtils;
import cn.smvc.sys.dao.SysDictDao;
import cn.smvc.sys.entity.SysDict;

@Repository("sysDictDao")
public class SysDictDaoImpl implements SysDictDao {
	/*
	 * 这两个是处理数据库表字段下划线的，
	 * 		与实体类中的属性对应（驼峰规则）
	 */
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);
	BeanListHandler<SysDict> beanListHandler = new BeanListHandler<SysDict>(SysDict.class, processor);
	BeanHandler<SysDict> beanHandler = new BeanHandler<SysDict>(SysDict.class, processor);
	
	
	@Override
	public List<SysDict> findAll(SysDict dict) throws SQLException {
		String sql = "select * from sys_dict where del_flag=0";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<SysDict> list = null;
		list = queryRunner.query(sql, beanListHandler);
		return list;
	}

	@Override
	public SysDict findOne(String id) throws SQLException {
		String sql = "select * from sys_dict where id=? and del_flag=0";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		SysDict dict = null;
		dict = queryRunner.query(sql, beanHandler, id);
		return dict;
	}

	@Override
	public int save(SysDict dict) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int update(SysDict dict) throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}
	/**
	 * 通过id删除数据
	 * @param id
	 * @param type 0：真删除，1：假删
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int delete(String id, int type) throws SQLException {
		String sql = null;
		if(type == 0) {
			sql = "delete from sys_dict  where id=? ";
		} else if(type == 1) {
			sql = "update sys_dict set del_flag=1 where id=? ";
		}
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		int result = 0;
		try {
			// 用queryRunner保存信息
			result = queryRunner.update(JDBCUtils.getConnection(), 
					sql.toString(), id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		return result;
	}

	@Override
	public int count(String where) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sql = new StringBuilder();
		sql
		.append("select count(*) from sys_dict where ")
		.append(where);
		Long count = queryRunner.query(sql.toString(), new ScalarHandler<Long>());
		return count != null ? count.intValue() : 0;
	}

	@Override
	public List<SysDict> findBySql(String sql) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<SysDict> list = null;
		list = queryRunner.query(sql, beanListHandler);
		return list;
	}

	@Override
	public List<SysDict> findByWhere(String where) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sb = new StringBuilder();
		sb.append("select * from sys_dict where ").append(where);
		List<SysDict> list = null;
		list = queryRunner.query(sb.toString(), beanListHandler);
		return list;
	}

	@Override
	public List<SysDict> findPage(String where, int page, int limit, String orderBy) throws SQLException {
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		StringBuilder sb = new StringBuilder();
		sb.append("select * from sys_dict where ").append(where);
		if(StringUtils.isNotEmpty(orderBy)) {
			sb.append(" order by ").append(orderBy);
		}
		sb.append(" limit ").append((page - 1) * limit).append(", ").append(limit);
		List<SysDict> list = null;
		list = queryRunner.query(sb.toString(), beanListHandler);
		return list;
	}

	@Override
	public List<SysDict> findAllType() throws SQLException {
		String sql = "select distinct type, description from sys_dict where del_flag=0";
		QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
		List<SysDict> list = null;
		list = queryRunner.query(sql, beanListHandler);
		return list;
	}

}
